import pandas as pd
from typing import Any, Dict, Type
from dataclasses import dataclass
from pathlib import Path
import black

import inspect
## author: Michael Zhang @2024-11-01
class Excel2Data:
    """
    A class to generate Python classes from Excel sheets.
    Each sheet will become a separate class with typed attributes based on the Excel columns.
    """
    
    def __init__(self, excel_path):
        """
        Initialize the generator with an Excel file path.
        
        Args:
            excel_path: Path to the Excel file
        """
        self.excel_path = Path(excel_path)
        if not self.excel_path.exists():
            raise FileNotFoundError(f"Excel file not found: {excel_path}")
        self.excel_data = pd.ExcelFile(self.excel_path)
        
    def get_available_sheets(self):
        """Return list of available sheet names in the Excel file."""
        return self.excel_data.sheet_names
    
    def _get_python_type(self, dtype: Any) -> Type:
        """Convert pandas dtype to Python type."""
        dtype_str = str(dtype)
        if 'int' in dtype_str:
            return int
        elif 'float' in dtype_str:
            return float
        elif 'datetime' in dtype_str:
            return 'datetime'  # Return as string for code generation
        elif 'bool' in dtype_str:
            return bool
        else:
            return str
    
    def generate_class_code(self, sheet_name: str) -> str:
        """
        Generate Python code string for the class.
        
        Args:
            sheet_name: Name of the Excel sheet
            
        Returns:
            String containing the Python code for the class
        """
        variables_dict = self._get_columns_dict(sheet_name)
        
        imports = set([
            '# define new class of variables tree '
        ])
        
        # Generate class code
        class_code = f"""
{chr(10).join(sorted(imports))}    

class {sheet_name}():
    \"\"\"
    Class generated from Excel sheet '{sheet_name}' in {self.excel_path.name}
    Generated by Excel2Data
    \"\"\"
"""
        
        # Add variable definitions in the requested format
        for var_name, attrs in variables_dict.items():
            class_code += f"    {var_name} = {attrs['type']}('{attrs['units']}', '{attrs['desc']}', '{attrs['desc_chinese']}', '{attrs['iotype']}')\n"
        print(class_code)
        return class_code

    def save_class_to_file(self, sheet_name: str, output_dir: str) -> Path:
        """
        Save generated class to a Python file.
        
        Args:
            sheet_name: Name of the Excel sheet
            output_dir: Directory to save the generated files
            
        Returns:
            Path to the generated file
        """
        output_dir = Path(output_dir)
        output_dir.mkdir(exist_ok=True)
        
        # Create __init__.py to make it a package
        init_file = output_dir / "__init__.py"
        if not init_file.exists():
            init_file.touch()
        
        # Generate the file path
        file_path = output_dir / f"{sheet_name.lower()}.py"
        
        # Generate and save the code
        code = self.generate_class_code(sheet_name)
        
        # Format with black if available
        try:
            import black
            code = black.format_str(code, mode=black.FileMode())
        except ImportError:
            pass
        
        file_path.write_text(code)
        
        return file_path

    def load_data(self, sheet_name):
        """
        Load data from sheet into class instances.
        
        Args:
            sheet_name: Name of the Excel sheet
            
        Returns:
            List of instances of the class
        """
        df = pd.read_excel(self.excel_path, sheet_name=sheet_name)
        return df.to_dict('records')

    def _get_columns_dict(self, sheet_name: str) -> Dict[str, Dict[str, Any]]:
        """
        Get variables and their descriptions as a dictionary.
        
        Args:
            sheet_name: Name of the Excel sheet
            
        Returns:
            Dictionary with variables as keys and their attributes as nested dictionaries
        """
        df = pd.read_excel(self.excel_path, sheet_name=sheet_name)
        variables_dict = {}
        
        for _, row in df.iterrows():
            var_name = row.get('Variables', '')
            if pd.notna(var_name) and var_name.strip():
                variables_dict[var_name] = {
                    'type': row.get('Type', 'Float'),  # Default to Float if not specified
                    'units': row.get('Units', ''),
                    'desc': row.get('Desc', ''),
                    'desc_chinese': row.get('Variable_Chinese', ''),
                    'iotype': row.get('IOType', '')
                }
        
        return variables_dict

def main():
    """
    Main function to demonstrate Excel2Data usage and generate Python files.
    """
    import sys
    
    def print_separator():
        print("\n" + "="*50 + "\n")
    
    try:
        # Get Excel file path and output directory
        excel_path = sys.argv[1] if len(sys.argv) > 1 else "Framework_variables_v1.xlsx"
        output_dir = sys.argv[2] if len(sys.argv) > 2 else "output_excel"
        
        print(f"Using Excel file: {excel_path}")
        print(f"Output directory: {output_dir}")
        print_separator()
        
        # Initialize generator
        generator = Excel2Data(excel_path)
        sheets = generator.get_available_sheets()
        # Process each sheet
        for sheet_name in sheets:
            print(f"Processing sheet: {sheet_name}")
            file_path = generator.save_class_to_file(sheet_name, output_dir)
            print(f"Generated: {file_path}")
            print_separator()        
    except Exception as e:
        print(f"Error occurred: {str(e)}")
    # script to merge all class_name.py into one file named turbine.py
    try:
        import subprocess
        subprocess.run(["sh", "./merge_class.sh"], check=True)
    except subprocess.CalledProcessError as e:
        print(f"Error occurred in shell script execution: {str(e)}")
    except Exception as e:
        print(f"Error occurred in run shell script: {str(e)}")
if __name__ == "__main__":
    main()